CREATE TABLE [dbo].[SolicitationMain]
(
[SolicitationKey] [uniqueidentifier] NOT NULL,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SolicitationStatusCode] [int] NOT NULL CONSTRAINT [DF_SolicitationMain_SolicitationStatusCode] DEFAULT ((0)),
[CostCollection] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetRevenue] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SolicitationMain_TargetRevenue] DEFAULT ((0)),
[TotalRevenue] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SolicitationMain_TotalRevenue] DEFAULT ((0)),
[PredictedResponseRate] [decimal] (5, 4) NOT NULL CONSTRAINT [DF_SolicitationMain_PredictedResponseRate] DEFAULT ((0)),
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[ReminderDate] [datetime] NULL,
[UpdatedOn] [datetime] NOT NULL,
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[LowResponseAmount] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SolicitationMain_LowResponseAmount] DEFAULT ((0)),
[HighResponseAmount] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SolicitationMain_HighResponseAmount] DEFAULT ((0)),
[ActualCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SolicitationMain_ActualCost] DEFAULT ((0)),
[EstimatedCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SolicitationMain_EstimatedCost] DEFAULT ((0)),
[ExtendedCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SolicitationMain_ExtendedCost] DEFAULT ((0)),
[OverheadCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_SolicitationMain_OverheadCost] DEFAULT ((0)),
[FirstResponseDate] [datetime] NULL,
[LastResponseDate] [datetime] NULL,
[TotalPositiveResponse] [int] NOT NULL CONSTRAINT [DF_SolicitationMain_TotalPositiveResponse] DEFAULT ((0)),
[TotalNegativeResponse] [int] NOT NULL CONSTRAINT [DF_SolicitationMain_TotalNegativeResponse] DEFAULT ((0)),
[TotalSolicited] [int] NOT NULL CONSTRAINT [DF_SolicitationMain_TotalSolicited] DEFAULT ((0)),
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[AppealKey] [uniqueidentifier] NULL,
[AccessKey] [uniqueidentifier] NOT NULL,
[MarkedForDeleteOn] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_SolicitationMain_Delete]
    ON [dbo].[SolicitationMain]
    FOR DELETE
AS
UPDATE    ap1
SET    ap1.TotalRevenue = ap1.TotalRevenue - deleted.TotalRevenue
FROM    AppealMain ap1
    INNER JOIN vBoSolicitation sm1 ON ap1.AppealKey = sm1.AppealKey
    INNER JOIN deleted ON sm1.SolicitationKey = deleted.SolicitationKey

GO
CREATE  TRIGGER [dbo].[asi_SolicitationMain_Insert_Update]
    ON [dbo].[SolicitationMain]
    FOR INSERT, UPDATE
AS
UPDATE    ap1
   SET    ap1.TotalRevenue = Coalesce(
        (SELECT    Sum(sm2.TotalRevenue)
         FROM    AppealMain ap2
                INNER JOIN vBoSolicitation sm2 ON ap2.AppealKey = sm2.AppealKey
         WHERE    ap2.AppealKey = ap1.AppealKey), 0)
  FROM    AppealMain ap1
        INNER JOIN vBoSolicitation sm1 ON ap1.AppealKey = sm1.AppealKey
        INNER JOIN inserted ON sm1.SolicitationKey = inserted.SolicitationKey

GO
ALTER TABLE [dbo].[SolicitationMain] ADD CONSTRAINT [PK_SolicitationMain] PRIMARY KEY CLUSTERED ([SolicitationKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SolicitationMain_AccessKey] ON [dbo].[SolicitationMain] ([AccessKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SolicitationMain_AppealKey] ON [dbo].[SolicitationMain] ([AppealKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SolicitationMain_CreatedByUserKey] ON [dbo].[SolicitationMain] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SolicitationMain_SolicitationStatusCode] ON [dbo].[SolicitationMain] ([SolicitationStatusCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SolicitationMain_UpdatedByUserKey] ON [dbo].[SolicitationMain] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SolicitationMain] ADD CONSTRAINT [FK_SolicitationMain_AccessMain] FOREIGN KEY ([AccessKey]) REFERENCES [dbo].[AccessMain] ([AccessKey])
GO
ALTER TABLE [dbo].[SolicitationMain] ADD CONSTRAINT [FK_SolicitationMain_AppealMain_AppealKey] FOREIGN KEY ([AppealKey]) REFERENCES [dbo].[AppealMain] ([AppealKey])
GO
ALTER TABLE [dbo].[SolicitationMain] ADD CONSTRAINT [FK_SolicitationMain_SolicitationStatusRef] FOREIGN KEY ([SolicitationStatusCode]) REFERENCES [dbo].[SolicitationStatusRef] ([SolicitationStatusCode])
GO
ALTER TABLE [dbo].[SolicitationMain] ADD CONSTRAINT [FK_SolicitationMain_UniformRegistry] FOREIGN KEY ([SolicitationKey]) REFERENCES [dbo].[UniformRegistry] ([UniformKey])
GO
ALTER TABLE [dbo].[SolicitationMain] ADD CONSTRAINT [FK_SolicitationMain_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[SolicitationMain] ADD CONSTRAINT [FK_SolicitationMain_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO